﻿using SIE.SCADA.ZemtDemo.MqClient.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;
using SIE.SCADA.SqlSugar;
using Aspose.Cells;
using QwUI;
using QwUI.WpfControl;
using System.IO;
using System.Data;
using SIE.SCADA.Common.Log;

namespace SIE.SCADA.ZemtDemo.Common
{
    class QuosExport
    {
        PgSqlAccess pgsql = new PgSqlAccess();
        List<TraQ> traQs = new List<TraQ>();

        public TraQ GetQuosData(string disidNo, string sn)
        {
            List<QuosDetail> quosDetails = new List<QuosDetail>();
            TraQ res = new TraQ();
            List<scada_process_record> processes = new List<scada_process_record>();
            List<scada_jobstep_record> jobSteps = new List<scada_jobstep_record>();
            List<scada_disprocess> disidProcess = new List<scada_disprocess>();
            smdc_disid_t smdc_Disid = new smdc_disid_t();

            using (var ctx = pgsql.GetClient())
            {
                try
                {
                    if (pgsql.DBClient is SmdcOrmClient ormClient)
                    {
                        processes = ormClient.SqlSugarClient.Queryable<scada_process_record>().Where(m => m.serial_number == sn && m.disidno == disidNo).
                            OrderBy(m => m.jobstep_code).OrderBy(m => m.disque_code).ToList();

                        jobSteps = ormClient.SqlSugarClient.Queryable<scada_jobstep_record>().
                            Where(m => m.serial_number == sn && m.disidno == disidNo).ToList();

                        disidProcess = ormClient.SqlSugarClient.Queryable<scada_disprocess>().
                            Where(m => m.serial_number_str == sn && m.disidno == disidNo).ToList();

                        smdc_Disid = ormClient.SqlSugarClient.Queryable<smdc_disid_t>().
                            Where(m => m.serial_number == sn && m.disidno == disidNo).First();
                    }
                 }
                catch (Exception ex)
                {
                    Logger.Error(ex.Message);
                }
            }

            foreach (var process in processes)
            {
                var quos = jobSteps.Where(m => m.jobstep_code_str.Equals(process.jobstep_code)
                && m.disidno == disidNo
                && m.serial_number == sn
                //&& m.element_version_str.Equals(StepTypeCont.Quo)
                
                ).FirstOrDefault();


                var dis = disidProcess.Where(m => m.processcode_str.Equals(process.disque_code)
                && m.disidno == disidNo
                && m.serial_number_str == sn).FirstOrDefault();
                var details = jobSteps.Where(m => m.jobstep_code_str.Equals(process.jobstep_code)
                && m.disidno == disidNo
                && m.serial_number == sn
                && m.element_type_str.Equals(StepTypeCont.Detail)).ToList();

                //string detailDesc = string.Empty;
                StringBuilder detailDesc = new StringBuilder();
                foreach (var detail in details)
                {
                    detailDesc = detailDesc.Append(detail.jobstep_str);
                }

                QuosDetail quosdetail = new QuosDetail()
                {
                    DisqName = process?.disque_name,
                    JobSteNo = process?.jobstep_code,
                    QualityDesc = quos?.jobstep_str,
                    Remark = quos?.remark_str,
                    StartTime = process?.createtime_dt,
                    EndTime = process?.modifytime_dt,
                    Checker = dis?.checkuserid_str,
                    Opeartor = dis?.userid_str,
                    Detail = detailDesc.ToString()
                };
                quosDetails.Add(quosdetail);
            }
            res.QuosDetails = quosDetails;
            res.Matid = smdc_Disid.matid;
            res.Matdesc = smdc_Disid.matdesc;
            return res;
        }

        /// <summary>
        /// 创建作业指导书
        /// </summary>
        /// <param name="disidNo"></param>
        /// <param name="sn"></param>
        public void CreateQuos(string disidNo, string sn)
        {
            try
            {
                List<scada_process_record> processes = new List<scada_process_record>();
                List<scada_jobstep_record> jobSteps = new List<scada_jobstep_record>();
                List<scada_disprocess> disidProcess = new List<scada_disprocess>();
                List<v_scada_jobsteprecord> V_jobSteps = new List<v_scada_jobsteprecord>();
                smdc_disid_t smdc_Disid = new smdc_disid_t();

                using (var ctx = pgsql.GetClient())
                {
                    try
                    {
                        if (pgsql.DBClient is SmdcOrmClient ormClient)
                        {
                            processes = ormClient.SqlSugarClient.Queryable<scada_process_record>().Where(m => m.serial_number == sn && m.disidno == disidNo).
                                OrderBy(m => m.jobstep_code).OrderBy(m => m.disque_code).ToList();

                            jobSteps = ormClient.SqlSugarClient.Queryable<scada_jobstep_record>().
                                Where(m => m.serial_number == sn && m.disidno == disidNo).ToList();

                            disidProcess = ormClient.SqlSugarClient.Queryable<scada_disprocess>().
                                Where(m => m.serial_number_str == sn && m.disidno == disidNo).ToList();

                            smdc_Disid = ormClient.SqlSugarClient.Queryable<smdc_disid_t>().
                                Where(m => m.serial_number == sn && m.disidno == disidNo).First();

                            V_jobSteps = ormClient.SqlSugarClient.Queryable<v_scada_jobsteprecord>().
                                Where(m => m.serial_number == sn && m.disidno == disidNo).ToList();
                        }
                    }
                    catch (Exception ex)
                    {
                        Logger.Error(ex.Message);
                    }
                }

                #region 样式
                Aspose.Cells.Style style = new Style();
                style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 左边界线  
                style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 右边界线  
                style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 上边界线  
                style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 下边界线   
                style.HorizontalAlignment = TextAlignmentType.Left; //单元格内容的水平对齐方式文字居中
                style.VerticalAlignment = TextAlignmentType.Center;
                style.IsTextWrapped = true; //自动换行
                style.Font.Name = "宋体"; //字体
                                        //style1.Font.IsBold = true; //设置粗体
                style.Font.Size = 10; //设置字体大小
                                      //style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); //背景色
                                      //style.Pattern = Aspose.Cells.BackgroundType.Solid;  
                #endregion
                var templete = new ExcelTemplet(Properties.Resources.质量跟踪卡);
                var sheet = templete.GetTempletSheet("产品组装质量跟踪卡");
                Cells cells = sheet.Cells;
                cells["C3"].PutValue(smdc_Disid.matdesc);
                cells["G5"].PutValue(smdc_Disid.matid);
                cells["C7"].PutValue("机电车间");
                cells["J7"].PutValue(smdc_Disid.createtime_dt.ToString());
                cells["J8"].PutValue(smdc_Disid.modifytime_dt.ToString());
                int curRow = 9;
                foreach (var disprocess in disidProcess)
                {
                    var processCode = disprocess.processcode_str;
                    var prcessName = disprocess.processname_str;
                    var disqSteps = V_jobSteps.Where(m => m.disque_code == processCode).OrderBy(m => m.jobstep_code).ToList();
                    var startRow = curRow;
                    var count = 0;
                    var curProcess = processes.Where(m => m.disque_code == disprocess.processcode_str).OrderBy(m => m.disque_code).ToList();
                    foreach (var process in curProcess)
                    {
                        var steps = jobSteps.Where(m => m.jobstep_code_str == process.jobstep_code).OrderBy(m => m.jobstep_code_str).ToList();

                        var quos = jobSteps.Where(m => m.jobstep_code_str.Equals(process.jobstep_code)
                           && m.disidno == disidNo
                           && m.serial_number == sn
                           && m.element_type_str.Equals(StepTypeCont.Quo)).FirstOrDefault();

                        var details = jobSteps.Where(m => m.jobstep_code_str.Equals(process.jobstep_code)
                            && m.disidno == disidNo
                            && m.serial_number == sn
                            && m.element_type_str.Equals(StepTypeCont.Detail)).ToList();

                        //组成详细信息
                        StringBuilder detailDesc = new StringBuilder();
                        foreach (var detail in details)
                        {
                            detailDesc = detailDesc.Append(detail.jobstep_str);
                        }



                        cells[curRow, 0].PutValue(process.jobstep_code);
                        cells[curRow, 0].SetStyle(style);
                        cells[curRow, 1].PutValue(process.disque_name);
                        cells[curRow, 1].SetStyle(style);

                        cells[curRow, 2].PutValue(detailDesc.ToString());
                        cells[curRow, 2].SetStyle(style);
                        cells[curRow, 3].SetStyle(style);
                        cells[curRow, 4].SetStyle(style);
                        cells.Merge(curRow, 2, 1, 3);

                        cells[curRow, 5].PutValue(quos?.jobstep_str);
                        cells[curRow, 5].SetStyle(style);
                        cells[curRow, 6].SetStyle(style);
                        cells.Merge(curRow, 5, 1, 2);

                        cells[curRow, 7].PutValue(process.createtime_dt.ToString());
                        cells[curRow, 7].SetStyle(style);
                        cells[curRow, 8].PutValue(process.modifytime_dt.ToString());
                        cells[curRow, 8].SetStyle(style);
                        cells[curRow, 9].PutValue(disprocess.userid_str);
                        cells[curRow, 9].SetStyle(style);
                        cells[curRow, 10].PutValue(disprocess.checkuserid_str);
                        cells[curRow, 10].SetStyle(style);
                        cells[curRow, 11].PutValue(quos?.remark_str);
                        cells[curRow, 11].SetStyle(style);
                        curRow++;
                        count++;
                    }
                    cells.Merge(startRow, 1, count, 1);
                    cells.Merge(startRow, 7, count, 1);
                    cells.Merge(startRow, 8, count, 1);
                    cells.Merge(startRow, 9, count, 1);
                    cells.Merge(startRow, 10, count, 1);
                }
                #region 为了输出日期
                for (int i = 0; i < 12; i++)
                {
                    cells[curRow, i].SetStyle(style);
                }
                cells.Merge(curRow, 3, 1, 3);

                cells[curRow, 7].PutValue("日期");
                cells.Merge(curRow, 8, 1, 4);
                cells[curRow, 8].PutValue(DateTime.Now.ToString());
                #endregion
                //sheet.AutoFitColumns();
                Aspose.Cells.AutoFitterOptions fitOptions = new Aspose.Cells.AutoFitterOptions();
                fitOptions.AutoFitMergedCells = true;
                sheet.AutoFitRows(fitOptions);
                sheet.AutoFitColumns(fitOptions);
                var path = $@"D:\质量追踪卡";
                if (!System.IO.Directory.Exists(path))
                {
                    System.IO.Directory.CreateDirectory(path);//不存在就创建目录
                }
                var fileName = Path.Combine(path, $"{disidNo}_{sn}.xlsx");
                DataTableExport(sheet, fileName);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine($"生成质量跟踪有误！{ex.Message}");
            }
        }

        /// <summary>
        /// DataTable数据导出Excel
        /// </summary>
        /// <param name="data"></param>
        /// <param name="filepath"></param>
        public static void DataTableExport(DataTable data, Worksheet worksheet, string filepath)
        {
            try
            {
                Workbook book = new Workbook(); //创建工作簿
                Worksheet sheet = book.Worksheets[0]; //创建工作表
                sheet.Copy(worksheet);
                Cells cells = sheet.Cells; //单元格
                                           //创建样式
                Aspose.Cells.Style style = book.Styles[book.Styles.Add()];
                style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 左边界线  
                style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 右边界线  
                style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 上边界线  
                style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 下边界线   
                style.HorizontalAlignment = TextAlignmentType.Center; //单元格内容的水平对齐方式文字居中
                style.Font.Name = "宋体"; //字体
                                        //style1.Font.IsBold = true; //设置粗体
                style.Font.Size = 11; //设置字体大小
                                      //style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); //背景色
                                      //style.Pattern = Aspose.Cells.BackgroundType.Solid;  

                int Colnum = data.Columns.Count;//表格列数 
                int Rownum = data.Rows.Count;//表格行数 
                                             //生成行 列名行 
                for (int i = 0; i < Colnum; i++)
                {
                    cells[0, i].PutValue(data.Columns[i].ColumnName); //添加表头
                    cells[0, i].SetStyle(style); //添加样式
                }
                //生成数据行 
                for (int i = 10; i < Rownum; i++)
                {
                    for (int k = 0; k < Colnum; k++)
                    {
                        cells[1 + i, k].PutValue(data.Rows[i][k].ToString()); //添加数据
                        cells[1 + i, k].SetStyle(style); //添加样式
                    }
                }
                sheet.AutoFitColumns(); //自适应宽
                book.Save(filepath); //保存
                GC.Collect();
            }
            catch (Exception ex)
            {
                Logger.Error(ex.Message);
                Logger.Error(ex.StackTrace);
            }
        }

        public static void DataTableExport(Worksheet worksheet, string filepath)
        {
            try
            {
                Workbook book = new Workbook(); //创建工作簿
                Worksheet sheet = book.Worksheets[0]; //创建工作表
                sheet.Copy(worksheet);
                book.Save(filepath); //保存
                GC.Collect();
            }
            catch (Exception e)
            {
                System.Diagnostics.Trace.WriteLine($"DataTableExport 执行失败！{e.Message}");
            }
        }
    }
}
